Method of parallel trigger execution in an active database

ABSTRACT

A method for executing after-triggers in an active database. A tree is constructed for each after-trigger and an operator tree is constructed for the statement that activates the trigger. The method joins each of the trees for the activated row-after triggers to the operator tree for pipelined execution with the operator tree. The trees for the activated row-after triggers form a group and each of the trees within the group execute in parallel with each other. The method joins trees for activated statement-after triggers to the operator tree for execution subsequent to the execution of the operator tree, the statement after trigger trees receiving rows from a temporary table that accumulates affected rows from the operator tree. Trees for activated statement after triggers form a group and each of the trees within the group execute in parallel with each other.

CROSS-REFERENCE TO RELATED APPLICATIONS

[0001] This application is a continuation of copending application Ser.No. 09/823,337, filed Mar. 29, 2001, which is hereby incorporated byreference herein. This application is related to U.S. applicationentitled “A METHOD OF EXECUTING CONFLICTING TRIGGERS IN AN ACTIVEDATABASE”, Ser. No. 09/823,340, filed on Mar. 29, 2001; and to U.S.application entitled “A METHOD OF EXECUTING BEFORE-TRIGGERS IN AN ACTIVEDATABASE, Ser. No. 09/822,996, filed on Mar. 29, 2001.

FIELD OF THE INVENTION

[0002] The present invention relates generally to executing triggers inactive relational databases and more specifically to the concurrentexecution of after-triggers in a relational data base management system.

DESCRIPTION OF THE RELATED ART

[0003] Database management systems (DBMS) 11, such as the system shownin FIG. 1, have become the dominant means of keeping track of data,especially for servers connected to the Internet. These systems take anorganized approach to the storage of data by imposing a data model,typically a relational data model, on the data 17 that is stored in thedatabase 15. Included in the typical DBMS are a Query Processing Engine13, a File Access and Storage Management subsystem 21 for accessing thedatabase 15, a Concurrency Control subsystem 19 for managing locksneeded for concurrency on database items (tables and rows) and aRecovery Control Subsystem 23 for restoring the DBMS 23 to a consistentstate after a fatal error. The latter two subsystems 19, 23, areinterconnected with the File Access and Storage Management subsystem 21.

[0004] In the relational data model, data is stored as a relation, whichhas two aspects, the relation schema and the relation instance. Therelation schema specifies the relation's name, and the name and domainof each column in the relation. The relation instance is a set ofrecords (also called rows or tuples) that conform to the relationschema. A relation instance is therefore a table of records, each ofwhich has a column that meets the domain constraints imposed by theschema.

[0005] Not only does the DBMS impose a constraint on storage of data, aDBMS usually formalizes the means by which information may be requestedfrom the database. In particular, a query language is specified by whichquestions may be put to the database. The language is usually based on aformal logic structure such as relational algebra or calculus. Queriesare usually carried out in the DBMS 11 by a Query Processing Engine 13,which has a number of components for parsing a query, creating a queryplan, and evaluating the query plan. In particular, a component of theQuery Processing Engine 13, a Query Optimizer, creates one or more queryplans, each in the form of a tree of relational operators, that areevaluated for execution of the query based on some efficiency metric.

[0006] Relational operators take one or more tables as inputs andgenerate a new table as the output. For example, a selection operatorselects one or more rows of an input table meeting the selectioncriteria to produce an output table having only those rows. Operatorscan be composed since an operator may take as input a table generated asthe output of another operator. A tree of operators is therepresentation of a composition of the relational operators appearing asthe nodes of the tree.

[0007] A tree of such operators for a particular query plan is shown inFIG. 3. As can be observed from the tree of FIG. 3, relational operatorsare connected to each other and to base tables T1 and T2 by means ofqueues Q1-Q4. These queues supply input rows to a particular operatorand store output rows from the operator. The queues allow an operator tostart processing rows as soon as the operator that supplies the rowsbegins to produce them and before all rows are produced. Such pipeliningimproves the efficiency of the system because intermediate results neednot be stored in a temporary table and then read again for input.

[0008] The standard language for implementing a DBMS is the StructuredQuery Language (SQL). This language includes Triggers, which are actionsexecuted by the DMBS under certain conditions.

[0009] A database having a set of triggers is called an active databaseand each trigger in the database has three parts, an event, a conditionand an action. The event part is a change to the database, such as aninsertion, deletion, or modification of a table, that activates thetrigger. The SQL statement which is the activating event, is termed theactivating statement. A condition is a test by the activated trigger todetermine whether the trigger action should occur and an action is anSQL statement that is executed if the trigger event and triggercondition are both satisfied. The set of rows affected (i.e., inserted,updated, or deleted) by the activating statement is termed the affectedset of rows for the relevant trigger.

[0010] The action part of the trigger can occur either before or afterthe activating statement. If before, it is called a before-trigger andif after, it is called an after-trigger. In addition, triggers canoperate at the row level or the statement level. A statement triggerexecutes its action once per activating statement and a row triggerexecutes its action for each row in the affected set. The combination of“before” and “after” with “row” and “statement” creates four differenttypes of triggers. Chain reactions of trigger actions and recursivetrigger actions are also possible.

[0011] The execution of triggers in a relational database is governed bythe proposed ANSI standard for SQL (SQL:1999) which places certainrestrictions on trigger execution. A chief restriction is that thetriggers be executed serially in their creation time order or at leastthat the serial execution of triggers be equivalent in outcome andeffect on the database to the execution of triggers in their creationtime order. However, the serial execution of triggers, in accordancewith the proposed ANSI:99 standard, would seriously affect theperformance of the DMBS, especially if many trigger actions areinvolved. Thus, there is a need for the improved execution of multipletrigger actions which leads to improved performance of trigger actionsover a purely sequential execution, but still conforms to the ANSIstandard.

BRIEF SUMMARY OF THE INVENTION

[0012] The present invention is directed towards the above need. Amethod of forming an execution plan in accordance with the presentinvention includes the following steps. First, any triggers that may beactivated by an activating statement and any rows in database tablesthat are affected by the activating statement are determined. Anoperator tree for the activating statement is then formed and a tree forthe trigger that is activated by the activating statement is formed. Theactivated trigger is either a row-after trigger or a statement-aftertrigger. If the activated trigger is a row-after trigger, the tree forthe row-after trigger is joined to the operator tree for pipelinedexecution with the operator tree and any rows affected by the activatingstatement are pipelined to the row-after trigger for input. If theactivated trigger is a statement-after trigger, the tree for thestatement-after trigger is joined to the operator tree for executionsubsequent to the operator tree. The statement-after trigger obtainsinput during execution from a temporary table that accumulates affectedrows from the execution of the activating statement.

[0013] If a plurality of row-after triggers is activated by theactivating statement, each of the trees for the row-after triggers isjoined to the operator tree for pipelined execution with the operatortree. In one embodiment, the plurality of trees for activated row-aftertriggers is connected to a parallel union operator to form a group and aflow operator is interconnected between the parallel union operator andthe operator tree.

[0014] If a plurality of statement after triggers is activated by theactivating statement, each of the statement-after trigger trees isjoined to the operator tree for execution subsequent to the execution ofthe operator tree. In one embodiment, the activated statement-afteractions are connected to a parallel union operator to form a group, aflow operator is interconnected between the operator tree and atemporary table that accumulates affected rows from the operator treeand an ordered union operator is interconnected between the parallelunion operator and the flow operator.

[0015] Joining both a plurality of activated row-after triggers and aplurality of statement-after triggers to the operator tree is such thatthe activated row-after triggers execute in a pipelined fashion with theoperator tree and the activated statement-after triggers executesubsequently to the execution of the operator tree. Each trigger treewithin either the statement-after group or the row-after group executesin parallel with the other trigger trees in the group.

[0016] An advantage is that row after-triggers are executedsubstantially in parallel with each other and in a pipeline with theexecution of the operator tree for the activating statement therebysubstantially reducing the execution time of row-after triggers comparedto purely sequential execution of the activating statement and thetriggers.

[0017] Another advantage is that statement-after triggers are executedsubstantially in parallel with each other thereby substantially reducingthe execution time of statement-after triggers compared to the purelysequential execution of the activating statement and the triggers.

[0018] Another advantage of the invention is that triggers execute inparallel with the activating statement and groups of triggers that areactivated by the same activating statement execute in parallel.

BRIEF DESCRIPTION OF THE DRAWINGS

[0019] These and other features, aspects and advantages of the presentinvention will become better understood with regard to the followingdescription, appended claims, and accompanying drawings where:

[0020]FIG. 1 illustrates a typical database management system;

[0021]FIG. 2A illustrates a Flow operator;

[0022]FIG. 2B illustrates an Ordered Union Operator;

[0023]FIG. 2C illustrates a Parallel Union Operator;

[0024]FIG. 3 shows an operator tree for a statement;

[0025]FIG. 4 shows a trigger tree and a representative statement for atrigger;

[0026]FIG. 5 shows an overview of an aspect of the present invention;

[0027]FIG. 6A illustrates a more detailed execution plan in accordancewith the present invention;

[0028]FIG. 6B illustrates a timing chart for the plan of FIG. 6A; and

[0029]FIG. 7 shows a flow chart for creating an execution plan inaccordance with the present invention.

DETAILED DESCRIPTION OF THE INVENTION

[0030] The present invention relies on a number of operators to controlthe execution of operations in both an activating statement and itsassociated trigger trees. The first of these operators is illustrated inFIG. 2A which shows a Flow Operator. The function of this operator is tomove the output of operator op1 12 to the input of operator op2 14, asthe output of operator op1 is produced. For example, if op1 is aselection operator on a table which selects rows of the table meeting acertain condition, then as the rows meeting the condition are found, sayby scanning the table, the rows are sent to the input of op2. Thispermits the op2 operator to function in parallel to the op1 operator,though, of course, not on the same row that op1 is operating on. FIG. 2Aillustrates this “pipelining” operation in a timing chart which showsthe activity of op1 overlapped with the activity of op2.

[0031]FIGS. 2B and 2C illustrate the Union Operators. The Ordered Unionoperator 16 of FIG. 2B forces op2 to operate only after op1 hascompleted its operations, in effect serializing the op1, op2 operationsas shown in the timing chart. The Parallel Union operator 18 allows op2to operate concurrently with op1, and assumes that op2 has no dataaccess conflict with op1. As is evident from FIGS. 2A and 2C, the flowoperator 10 and the parallel union operator 18 reduce the time to carryout the functions of the op1 and op2 operators compared to the orderedunion operator 16.

[0032] Referring to FIG. 3, an operator tree 20 is shown for the givenSQL statement 22. The SQL statement 22 projects a desired column F1 fromthe table created by joining tables T1, T2 and selecting the rows thatmeet the conjunction of conditions C1, C2 and C3. The operator tree 20shows one way of implementing the SQL statement 22. According to thetree, first T1 and T2 are joined based on condition C1 by the joinoperator 24. Next, a selection operator 26 selects the rows of thejoined table that meet the condition which is the conjunction of C2 andC3. Finally, a projection operator 28 selects the column F1 from anyrows that result from the prior operations. As described above, thefunction of a Query Optimizer is to form alternative execution plans fora query so that the plans can be evaluated in terms of some performancemetric. The tree in FIG.3 is only one such tree that a Query Optimizercan produce for the given SQL statement.

[0033]FIG. 4 shows an SQL statement 30 for a row after-trigger, rt1. Theevent, condition and action for the trigger are shown in block 32. Theevent for rt1 is a row insertion into a table T1; the condition is C1,which can be an arbitrary relational condition and the ACTION part ofthe trigger can be practically any sequence of SQL statements. Thetrigger tree 34 represents both the condition and the action parts ofthe trigger.

[0034]FIG. 5 shows an overview of the present invention. In FIG. 5, anoperator tree 42 for an activating statement S is combined, i.e.,“inlined,” with a trigger tree 44 of a trigger T activated by thestatement to create an inlined tree 46. The inlined tree 46 is thenprocessed by an optimizer to create an optimized execution plan 50 forthe operators and trigger trees caused by the activating statement S.

[0035]FIG. 6A illustrates a more detailed execution plan formulated inaccordance with the present invention illustrated in FIG. 5. In FIGS. 6Aand 6B it is assumed that there are no data access conflicts among theactivated triggers and between the activated triggers and the activatingstatement and that all of the activated triggers are after-triggers.

[0036] Referring to FIG. 6A, statement S is represented by an operatortree 42, row triggers rt1 and rt2 are represented by trees 52, 54,respectively, and statement triggers st1 and st2 are represented bytrees 56 and 58, respectively. It is assumed that statement S is theevent that causes activation of the row and statement triggers. Inaccordance with the present invention, the operator tree 42 produces, asoutput, the set of affected rows. A flow operator 60 connects theoperator tree 42 for statement S to a temporary table, TempTable 62, sothat rows that are output by the operator tree 42 are pipelined to thetemporary table, TempTable 62. Parallel union operators 64 and 66connect the trees 52, 54 for rt1 and rt2 and the trees 56, 58 for st1and st2 so that trees 52 and 54 execute in parallel and trees 56 and 58execute in parallel.

[0037] Another flow operator 68 connects the parallel union operator 64for rt1 and rt2 to the flow operator 60 connected to the operator tree42 for statement S so that action trees 52 and 54 execute pipelined tothe execution of the statement tree 42. Finally, an ordered unionoperator 70 connects the flow operator 68 to the parallel union operator66 for st1 and st2 so that the trees 56 and 58 execute subsequent to theexecution of the statement tree 42. The statement trees 56 and 58receive their inputs by scanning the temporary table, TempTable 62, asrepresented by the scan functions 72 and 74.

[0038] The effect of structure of FIG. 6A is that the row triggersexecute in parallel with each other and pipelined with the activatingstatement and statement triggers execute in parallel with each other butsubsequent to the activating statement. Specifically, the structureoperates as follows. The operator tree 42 of S operates to generate astream of affected rows. As the operator tree for S produces the streamof rows, each row is pipelined by the flow operator 60 to the TempTable62 to prepare for the operation of the statement trigger st1 and st2,which must execute only after statement S is completed. TempTable 62accumulates the set of affected rows that were produced by the operatortree 42 for S. These changes may need to be made available to thestatement trigger trees st1 and st2. Additionally, each row produced bystatement S operator tree 42 is pipelined to the row trigger trees rt1and rt2, which execute in parallel on the pipelined rows. Uponcompletion of the execution of statement S, and the row triggers rt1 andrt2, the statement triggers st1 and st2 are allowed to execute becauseof the ordered union operator 70. The statement trigger trees execute inparallel with each other by scanning the TempTable 62 for input data asneeded. After the temporary table is used, the contents of the temporarytable are deleted by a special delete operator The timing of theexecution plan 76 of Statement S, rt1, rt2, st1 and st2, according tothe structure of FIG. 6A, is illustrated in FIG. 6B, where S representsthe time to execute the statement tree 42, rt1, the time to execute thert1 action tree 52, rt2 the time to execute the rt2 action tree 54, st1the time to execute the st1 action tree 56, and st2 the time to executethe st2 action tree 58. As can be noted from the figure, rt1 and rt2execute in parallel and overlap with the execution of statement Sbecause of pipelining. Statement triggers st1 and st2 execute inparallel but only after the execution of the row triggers. This gives alarge decrease in the time to execute the statement S and its associatedtriggers compared to the case of sequential execution 74 shown in thefigure.

[0039]FIG. 7 shows a flow chart of the process for creating an executionplan such as is shown in FIG. 6A. In the process depicted, first thetriggers that may be activated by the activating statement aredetermined in step 90 and an operator tree of the activating statementis formed in step 92. Next, a trigger tree for each of the activatedtriggers is formed in step 94 and, in step 95, the process then verifiesthat there are no conflicts among activated triggers and between theactivated triggers and the activating statement. An activated trigger iseither a row or statement trigger as determined by step 96. If a rowtrigger is activated, it is joined to the action tree for pipelinedexecution with the execution of the statement tree in step 98. If astatement trigger is activated, it is joined, in step 100, to thestatement tree for execution after the execution of the statement treeusing a temporary table as input for the action of the statementtrigger. The temporary table accumulates the set of affected rows. Thestatement trigger scans the temporary table for its input.

[0040] The above covers the case of a single row trigger or statementtrigger. If more than one row or statement trigger is activated by theactivating statement, the row or statement triggers must be combinedinto the execution plan. In particular, if a number of row triggers isactivated, the activated row triggers are combined together into aparallel row group (Group 1 in FIG. 6A) and this parallel row group isthe object that is attached to the statement tree for pipelinedexecution. Internal to the parallel group, each trigger isinterconnected by means of a parallel union operator to permit parallelexecution of each row trigger within the group. Thus, the execution planaccording to the present invention prescribes that each trigger in theparallel group executes in parallel with the other triggers in the groupand the entire group execute in a pipeline with the activating statementtree.

[0041] If a number of statement triggers is activated, the activatedstatement triggers are combined together into a parallel statement group(Group 2 in FIG. 6A) and this parallel statement group is the objectthat is attached to the statement tree for execution subsequent to thestatement tree. Again, internal to the parallel group, each trigger isinterconnected by means of a parallel union operator to permit parallelexecution of each statement trigger within the group. Additionally, eachstatement trigger during its execution typically scans the TempTable 62for its input. The execution plan thus prescribes that the statementtriggers execute in parallel and the entire group executes subsequent tothe execution of the activating statement tree.

[0042] Of course, it is possible that both a plurality of row triggersand a plurality of statement triggers are activated by the activatingstatement. This means that the final execution plan combines the actionstrees of both the activated statement triggers and row triggersaccording to FIG. 6A.

[0043] Although the present invention has been described in considerabledetail with reference to certain preferred versions thereof, otherversions are possible. Therefore, the spirit and scope of the appendedclaims should not be limited to the description of the preferredversions contained herein.

What is claimed is:
 1. A system, comprising: a database manager coupledto a database, the data base manager: joins a first trigger tree to anoperator tree by interconnecting a flow operator between the firsttrigger tree and the operator tree; and joins a second trigger tree toan operator tree by interconnecting a flow operator between a temporarytable and the operator tree and interconnecting an ordered unionoperator between the flow operator and the second trigger tree.
 2. Thesystem of claim 1 wherein the temporary table stores rows of an inputtable that are affected by execution of an activating statement.
 3. Thesystem of claim 2 wherein the rows of the input table that are affectedby execution of the activating statement are pipelined to a triggerassociated with the first trigger tree.
 4. The system of claim 1 whereinthe database manager joins a plurality of trigger trees to an operatortree by connecting each tree to a parallel union operator andinterconnecting a flow operator between the parallel union operator andthe operator tree.
 5. The system of claim 1 wherein the database managerjoins a plurality of trigger trees to an operator tree by connectingeach trigger tree to a parallel union operator, interconnecting anordered union operator between the parallel union operator and a flowoperator, and interconnecting the flow operator between the temporarytable and the operator tree.
 6. A method, comprising: executing a firsttype of triggers in parallel with each other; pipelining the first typeof triggers with an activating statement; and executing a second type oftriggers in parallel with each other and subsequent to the activatingstatement.
 7. The method of claim 6 further comprising creating atemporary table, wherein the temporary table comprises rows of a inputtable that are affected by the activating statement.
 8. The method ofclaim 7 further comprising providing the rows of the temporary table asinput to the first type of triggers.
 9. The method of claim 6 furthercomprising: forming a plurality of execution plans associated withexecuting the first type of triggers, pipelining the first type oftriggers with the activating statement, and executing the second type oftriggers; and evaluating the plans in terms of a performance metric.